---- Purpose: Loc ra cac phieu Nhap, xuat, VCNB de len man hinh truy van

----Purpose: Them cot so luong ton cuoi

--Purpose: Them he so quy doi cho mat hang
DROP PROCEDURE [dbo].[pr0003]
GO
CREATE      PROCEDURE [dbo].[pr0003] 	@DivisionID as varchar(20),
					@TranMonth as int,
					@TranYear as int,
					@WareHouseID as varchar(20) = '',
					@ConnID varchar(100) ='',
					@ToTranMonth as int=default,
					@ToTranYear as int=Default
 AS
Declare @sSQL as varchar(8000),
@strWhere as varchar(700),
@strWhere2 as varchar(700),
@strWhere1 as varchar(700)

if(@ToTranMonth is not null) --lay tu ky den ky
	Begin		
		set @strWhere=' Where tb0104.DivisionID ='''+@DivisionID+''' And (tb0104.TranMonth + tb0104.TranYear*100 Between ('+str(@TranMonth)+' + '+str(@TranYear)+'*100) and ('+str(@ToTranMonth)+' + '+str(@ToTranYear)+'*100))'
		set @strWhere2=' Where tb0105.DivisionID ='''+@DivisionID+''' And (tb0105.TranMonth + tb0105.TranYear*100 Between ('+str(@TranMonth)+' + '+str(@TranYear)+'*100) and ('+str(@ToTranMonth)+' + '+str(@ToTranYear)+'*100))'
		set @strWhere1=' And T06.TranYear*12 + T06.TranMonth <= ' + ltrim(@TranYear*12 + @TranMonth) + ''
	End
else	
	Begin
		set @strWhere=' Where tb0104.DivisionID ='''+@DivisionID+''' and tb0104.TranMonth ='+str(@TranMonth)+' and tb0104.TranYear ='+str(@TranYear)+''
		set @strWhere2=' Where tb0105.DivisionID ='''+@DivisionID+''' and tb0105.TranMonth ='+Str(@TranMonth)+' and tb0105.TranYear ='+Str(@TranYear)+''
		set @strWhere1=' And T06.TranYear*12 + T06.TranMonth <= ' + ltrim(@TranYear*12 + @TranMonth) + ''
	End
----- Buoc  1 : Tra ra thong tin Master View vi0000

Set @sSQL='
Select 	tb0038.VATNo as DivisionVATNo,tb0038.BankAccountNo As DivisionAccountNo,tb0104.ReDeTypeID,	
	tb0104.VoucherTypeID,
	VoucherNo,
	VoucherDate,
	tb0104.RefNo01,
	tb0104.RefNo02,
	tb0105.ConvertedAmount,
	tb0104.ObjectID+'' - '' + case when isnull(tb0104.VATObjectName,'''')='''' then ObjectName  else VATObjectName end as ObjectID,  
	tb0104.ObjectID As ObjectIDPermission,
	tb0045.ObjectName,
	tb0045.Address,
  
	tb0104.ContactPerson,
	tb0104.RDAddress   as DEAddress,

	tb0104.EmployeeID,tb0104.ShipperID,tb0104.Transport,tb0104.Note01,Note02,
	left(tb0104.Note02,2) As NoteDate,substring(tb0104.Note02,3,2) as NoteMonth,right(tb0104.Note02,4) as NoteYear,tb0104.Note03,tb0104.Note04,
	tb0104.ContractNo, tb0040.FullName,
	(Case when KindVoucherID in (1,3,5,7,9) then tb0104.WareHouseID Else '''' End) as ImWareHouseID,
	(Case when KindVoucherID in (2,4,6,8)  then tb0104.WareHouseID Else 
		Case When KindVoucherID =3 then tb0104.WareHouseID2 else '''' End End) as ExWareHouseID,
	tb0104.Description,	tb0104.VoucherID,		tb0104.OrderID,		tb0104.ProjectID,		tb0104.Status,		tb0104.DivisionID,	tb0104.TranMonth,
	tb0104.TranYear,	tb0104.CreateDate,             	tb0104.CreateUserID,            	tb0104.LastModifyUserID,            tb0104.LastModifyDate ,            
	tb0104.KindVoucherID,	tb0102.OrderNo as OrderNo,	tb0055.WareHouseName as WareHouseName,
	WE.WareHouseName as ExWareHouseName

From tb0104 Left join (Select VoucherID,SUM(ConvertedAmount) As ConvertedAmount FROM tb0105 '+@strWhere2+' Group By VoucherID) tb0105 ON tb0104.VoucherID=tb0105.VoucherID
		left join tb0038 on tb0038.DivisionID=tb0104.DivisionID
		left join tb0045 on tb0045.ObjectID =tb0104.ObjectID
		left join tb0102 on tb0102.OrderID =tb0104.OrderID
		left join tb0055 on tb0104.WareHouseID = tb0055.WarehouseID and tb0104.DivisionID = tb0055.DivisionID
		left join tb0055 as WE on tb0104.WareHouseID2 = WE.WarehouseID and tb0104.DivisionID = WE.DivisionID
		left join tb0040 on tb0040.EmployeeID =tb0104.EmployeeID and tb0040.DivisionID = tb0104.DivisionID '+@strWhere

--Where 	tb0104.DivisionID ='''+@DivisionID+''' and
	--tb0104.TranMonth ='+str(@TranMonth)+' and
	--tb0104.TranYear ='+str(@TranYear)+ ' '

--print @ssql
If not Exists (Select 1 From SysObjects Where Xtype ='V' and Name =  'vi0000')
	Exec('Create View  vi0000 as '+@ssql)
Else
	Exec('Alter View vi0000 as '+@ssql)


If not Exists (Select 1 From SysObjects Where Xtype ='V' and Name =  'vi0000' + @ConnID)
	Exec('Create View  vi0000' + @ConnID + ' as '+@ssql)
Else
	Exec('Alter View vi0000' + @ConnID + ' as '+@ssql)



----- Buoc  2 : Tra ra thong tin Detail View vi0019

Set @ssQL='
Select 	tb0038.VATNo as DivisionVATNo,tb0038.BankAccountNo As DivisionAccountNo,
	tb0104.ShipperID,tb0104.Transport,tb0104.Note01,tb0104.Note02,
	left(tb0104.Note02,2) As NoteDate,substring(tb0104.Note02,3,2) as NoteMonth,right(tb0104.Note02,4) as NoteYear,tb0104.Note03,tb0104.Note04,
	tb0104.ContractNo,tb0104.ReDeTypeID,		tb0104.VoucherTypeID,		tb0104.VoucherNo,		
	tb0104.VoucherDate,		
	tb0104.RefNo01,
	tb0104.RefNo02,
	tb0104.RDAddress,
	tb0104.ContactPerson,
	tb0054.InventoryTypeID,
	tb0104.ObjectID,	
	tb0104.VATObjectName,	
	(Case when tb0104.KindVoucherID in (1,3,5,7,9) then tb0104.WareHouseID Else '''' End) as ImWareHouseID,
	(Case when tb0104.KindVoucherID in (2,4,6,8)  then tb0104.WareHouseID Else 
		Case When tb0104.KindVoucherID =3 then tb0104.WareHouseID2 else '''' End End) as ExWareHouseID,
	tb0104.EmployeeID,	tb0105.TransactionID,        	tb0104.VoucherID,       	tb0105.InventoryID,	tb0054.InventoryName,        	tb0105.UnitID,	tb0056.UnitName,
       	ActualQuantity,       	UnitPrice,       	OriginalAmount,      	ConvertedAmount,     	tb0104.Description,	tb0104.TranMonth,	tb0104.TranYear,	tb0104.DivisionID,
       	SaleUnitPrice,       	SaleAmount,       	DiscountAmount,       	SourceNo,	DebitAccountID, 	CreditAccountID,	LocationID,
	ImLocationID, 	Ana01ID,             Ana02ID,             Ana03ID,	Ana04ID, Ana05ID,
	tb0105.Orders,        	LimitDate,	tb0105.Notes as Notes,
	tb0105.ConversionFactor,	tb0105.ReVoucherID,	tb0105.ReTransactionID ,	tb0102.OrderNo as OrderNo,
	tb0105.OrderID,
	tb0054.IsSource,	tb0054.IsLimitDate,	tb0054.IsLocation,
	tb0054.MethodID,
	V06.VoucherNo as ReVoucherNo,
	tb0054.AccountID,
	tb0054.Specification1,
	tb0054.Notes01, tb0054.Notes02, tb0054.Notes03,
	tb0105.PeriodID,
	tb0061.UnitID As ConversionUnitID,
	tb0061.ConversionFactor As ConversionFactor2,
	tb0061.Operator,
	M01.Description as PeriodName,
             tb0105.ProductID,
	AT02.InventoryName as ProductName,
	ActEndQty =
	(
	--Ton dau
	Isnull((Select Sum(Isnull(T17.ActualQuantity,0)) From tb0109 T16, tb0110 T17
	Where T16.VoucherID = T17.VoucherID
	And T16.DivisionID = ''' + @DivisionID + ''' 
	And T16.WareHouseID = ''' + @WareHouseID + ''' 
	And T17.InventoryID = tb0105.InventoryID),0) 
	+
	--Nhap trong ky
	Isnull((Select Sum(Isnull(T07.ActualQuantity,0)) From tb0104 T06, tb0105 T07
	Where T06.VoucherID = T07.VoucherID
	And T06.KindVoucherID In (1,3,5,7)
	And T06.DivisionID = ''' + @DivisionID + ''' 
	And T06.WareHouseID = ''' + @WareHouseID +''+@strWhere1+'''
	
	And T06.VoucherDate <= tb0104.VoucherDate 
	And T06.VoucherID Not In (Select sT06.VoucherID From tb0104 sT06 Where VoucherDate = tb0104.VoucherDate And sT06.CreateDate>=tb0104.CreateDate) 
	And T07.InventoryID = tb0105.InventoryID),0) 
	-
	(
	--Xuat thuong trong ky
	Isnull((Select Sum(Isnull(T07.ActualQuantity,0)) From tb0104 T06, tb0105 T07
	Where T06.VoucherID = T07.VoucherID
	And T06.KindVoucherID In (2,4,6,8)
	And T06.DivisionID = ''' + @DivisionID + ''' 
	And T06.WareHouseID = ''' + @WareHouseID + ''+@strWhere1+'''
	
	And T06.VoucherDate <= tb0104.VoucherDate 
	And T06.VoucherID Not In (Select sT06.VoucherID From tb0104 sT06 Where VoucherDate = tb0104.VoucherDate And sT06.CreateDate>=tb0104.CreateDate) 
	And T07.InventoryID = tb0105.InventoryID),0)
	+
	--Xuat VCNB trong ky
	Isnull((Select Sum(Isnull(T07.ActualQuantity,0)) From tb0104 T06, tb0105 T07
	Where T06.VoucherID = T07.VoucherID
	And T06.KindVoucherID In (3)
	And T06.DivisionID = ''' + @DivisionID + ''' 
	And T06.WareHouseID2 = ''' + @WareHouseID + ''+@strWhere1+'''
	
	And T06.VoucherDate <= tb0104.VoucherDate 
	And T06.VoucherID Not In (Select sT06.VoucherID From tb0104 sT06 Where VoucherDate = tb0104.VoucherDate And sT06.CreateDate>=tb0104.CreateDate) 
	And T07.InventoryID = tb0105.InventoryID),0)
	)
	)
     
From tb0105
		left join tb0038 on tb0038.DivisionID=tb0105.DivisionID
	 	inner join tb0054 on tb0054.InventoryID =tb0105.InventoryID
		Left join tb0056 on tb0056.UnitID = tb0105.UnitID
		inner join tb0104 on tb0104.VoucherID =tb0105.VoucherID
		Left join tb0102 on tb0102.OrderID =tb0104.OrderID

		Left join vi0148 V06 on V06.VoucherID = tb0105.ReVoucherID And
					V06.TransactionID =tb0105.ReTransactionID
		
		Left Join tb3014 M01 on M01.PeriodID =tb0105.PeriodID

		Left Join tb0054 as AT02 on  AT02.InventoryID =tb0105.ProductID
		Left join (Select InventoryID,Min(UnitID) As UnitID, Min(ConversionFactor) As ConversionFactor, Min(Operator) As Operator From tb0061 Group By InventoryID) tb0061 On tb0105.InventoryID = tb0061.InventoryID'+@strWhere2

--Where  	tb0105.DivisionID ='''+@DivisionID+''' and
	--tb0105.TranMonth ='+Str(@TranMonth)+' and
	--tb0105.TranYear ='+Str(@TranYear)+' '
--Print @sSQL
If not Exists (Select 1 From SysObjects Where Xtype ='V' and Name =  'vi0019')
	Exec('Create View  vi0019 as '+@ssql)
Else
	Exec('Alter View vi0019 as '+@ssql)

If not Exists (Select 1 From SysObjects Where Xtype ='V' and Name =  'vi0019' + @ConnID )
	Exec('Create View vi0019' +@ConnID + ' as '+@ssql)

Else
	Exec('Alter View vi0019'+ @ConnID +' as '+@ssql)


GO